﻿using Dapper;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using lycServo.entity;

namespace lycServo.dao
{
    /// <summary>
    /// 操作数据库的工厂类
    /// </summary>
    public class DBFactory
    {
        public static string conn_db = ConfigurationManager.ConnectionStrings["mysql"].ToString();

        public static MySqlConnection GetConnection()
        {
            MySqlConnection conn = new MySqlConnection(conn_db);
            conn.Open();
            return conn;
        }

        /// <summary>
        /// 仅查询符合条件的第一条记录（需使用limit 1）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static T QueryFirst<T>(string sql)
        {
            using (IDbConnection conn = new MySqlConnection(conn_db))
            {
                return conn.QueryFirstOrDefault<T>(sql);
            }
        }

        public static List<T> QueryList<T>(string sql)
        {
            using (IDbConnection conn = new MySqlConnection(conn_db))
            {
                return conn.Query<T>(sql).ToList();
            }
        }


        public static int BatchUpdateOfItem(List<PlcItemEntity> items)
        {
            using (IDbConnection conn = new MySqlConnection(conn_db))
            {
                string sql = "update PLC_ITEM set ITEM_VALUE=@ITEM_VALUE,OLD_VALUE=@OLD_VALUE,UPDATE_DATE=@UPDATE_DATE where ID=@ID";
                return conn.Execute(sql, items);
            }
        }

        public static int BatchUpdateOfMem(List<MemItemEntity> mems)
        {
            using (IDbConnection conn = new MySqlConnection(conn_db))
            {
                string sql = "update MEM_ITEM set ITEM_VALUE=@ITEM_VALUE,OLD_VALUE=@OLD_VALUE,UPDATE_DATE=@UPDATE_DATE where ID=@ID";
                return conn.Execute(sql, mems);
            }
        }

        public static int BatchUpdateOfPc(List<PcItemEntity> pcs)
        {
            using (IDbConnection conn = new MySqlConnection(conn_db))
            {
                string sql = "update PC_ITEM set ITEM_VALUE=@ITEM_VALUE,STATUS=@STATUS,UPDATE_DATE=@UPDATE_DATE where ID=@ID";
                return conn.Execute(sql, pcs);
            }
        }

        public static int UpdateStockById(StockEntity entity)
        {
            using (IDbConnection conn = new MySqlConnection(conn_db))
            {
                string sql = "update wcs_stock set locked_qty=@LOCKED_QTY,stock_st=@STOCK_ST,UPDATE_DATE=@UPDATE_DATE where ID=@ID";
                return conn.Execute(sql, entity);
            }
        }

        public static int UpdateStnMapByNo(StnMapEntity entity)
        {
            using (IDbConnection conn = new MySqlConnection(conn_db))
            {
                string sql = "update wcs_stn_mapping set status=@STATUS,UPDATE_DATE=@UPDATE_DATE where wcs_station=@WCS_STATION";
                return conn.Execute(sql, entity);
            }
        }

        public static void Insert2WcsTrk(TrkEntity entity)
        {
            string insertSql = $"INSERT INTO wcs_trk(request_pk,dvc_no,cont_no,cont_type,trk_type,trk_prty,frm_pos,to_pos,cur_pos,is_full,creator,create_date)" +
                $"VALUES(@REQUEST_PK,@DVC_NO,@CONT_NO,@CONT_TYPE,@TRK_TYPE,@TRK_PRTY,@FRM_POS,@TO_POS,@CUR_POS,@IS_FULL,@CREATOR,@CREATE_DATE)";
            using (IDbConnection conn = new MySqlConnection(conn_db))
            {
                conn.Execute(insertSql, entity);
            }
        }

        public static void Insert2WorkLog(LogEntity entity)
        {
            string insertSql = $"INSERT INTO wcs_log_work(work_name,cont_no,work_code,work_msg,write_item,work_proc,work_sec,creator,creator_name,create_date)" +
                $"VALUES(@WORK_NAME,@CONT_NO,@WORK_CODE,@WORK_MSG,@WRITE_ITEM,@WORK_PROC,@WORK_SEC,@CREATOR,@CREATOR_NAME,@CREATE_DATE)";
            using (IDbConnection conn = new MySqlConnection(conn_db))
            {
                conn.Execute(insertSql, entity);
            }
        }
    }
}
